/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package shoppinglist;

import java.sql.*;
import java.util.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Student
 */
public class DBConnection {

    private final String serverip = "85.224.154.41:125";
    private final String serverip2 = "192.168.1.68:3306";
    private PreparedStatement getliststmt;
    private PreparedStatement insertListtmt;
    private PreparedStatement insertGrocerystmt;
    Statement st;
    private Connection con = null;
    private String url = "jdbc:mysql://" + serverip + "/shoppinglist?user=da523b&password=b325ad";
    private String getliststr = "select grocery,qty from slGroceryList where list =?";
    private String insertList = "insert into slList values(?)";
    private String insertGrcry = "insert into slGroceryList values(?,?,?)";

    public DBConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            System.out.println("Failed to initialize DBConnector");
            ex.printStackTrace();
        }

    }

    public void connect() {
        try {
            con = DriverManager.getConnection(url);
        } catch (SQLException ex) {
            System.out.println("Failed to connect to MySql");
            ex.printStackTrace();
        }

        try {
            st = con.createStatement();
            getliststmt = con.prepareStatement(getliststr);
            insertListtmt = con.prepareCall(insertList);
            insertGrocerystmt = con.prepareStatement(insertGrcry);
        } catch (SQLException ex) {
            System.out.println("Failed to prepare statement.");
            ex.printStackTrace();
        }
           }

    public ResultSet getGroceries() {
        ResultSet rs = null;
        try {
            rs = st.executeQuery("SELECT name FROM slGroceries;");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return rs;
    }

    public String[] getShoppingLists() {
        ResultSet rs = null;
        try {

            rs = st.executeQuery("SELECT created FROM slList;");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        ArrayList<String> strlist = new ArrayList();
        try {
            while (rs.next()) {
                strlist.add(rs.getString(1));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        String[] StringArray = Arrays.copyOf(strlist.toArray(), strlist.toArray().length, String[].class);
        return StringArray;
    }

    public String[] getShoppingList(String date) {
        ResultSet rs = null;
        try {
            getliststmt.setString(1, date);
            rs = getliststmt.executeQuery();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        ArrayList<String> strlist = new ArrayList();
        try {
            while (rs.next()) {
                strlist.add(ListPost.getListPost(rs.getString(1), rs.getInt(2)));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        String[] StringArray = Arrays.copyOf(strlist.toArray(), strlist.toArray().length, String[].class);
        return StringArray;
    }

    public void saveShoppingList(String sh[]) {
        Date d = new Date();
        String da = d.toString();
        try {

            insertListtmt.setString(1, da);
            insertListtmt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        for (int i = 0; i < sh.length; i++) {
            try {
                insertGrocerystmt.setString(1, ListPost.getName(sh[i]));
                insertGrocerystmt.setInt(2, ListPost.getNumber(sh[i]));
                insertGrocerystmt.setString(3, da);
                insertGrocerystmt.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

    }

}
